package pf.java.pfHelperBackup;

import java.awt.List;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.UUID;

import pf.java.pfHelper.config.PFDataHelper;

public class SqlWhereCollection  extends ArrayList<SqlWhereItem>
{
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	public enum WhereOrAnd
    {
        Where ,
        And 
    }
//    /// <summary>
//    /// 分页参数
//    /// </summary>
//    public class Pagination
//    {
//        public Pagination(int start, int end, String sort)
//        {
//            Start = start;
//            End = end;
//            Sort = sort;
//        }
//        public int Start { get; set; }
//        public int End { get; set; }
//        /// <summary>
//        /// 分页参数的排序字段必需指定主键，否则会很慢
//        /// </summary>
//        public String Sort { get; set; }
//    }
    public void Add(String name, Object value, SqlExpressionOperator expressionOperator )
    {
        super.add(new SqlWhereItem(name, value, expressionOperator));
    }
    public void Add(String name, Object value)
    {
        this.Add(name, value,  SqlExpressionOperator.Equal);
    }
    /// <summary>
    /// 加中括号,如 a.typeid 变为 a.[typeid]
    /// </summary>
    private String FormatKey(String key)
    {
    	return key.replaceFirst("([^.]+)$", "[$1]");
//        return Regex.Replace(key, "([^.]+)$", "[$1]");
    }
    public String ToSql()
    {
    	return ToSql( WhereOrAnd.Where);
    }
    public String ToSql(WhereOrAnd woa)
    {
    	String result = "";
        int count = 0;
        String prev = "";
//        for (SqlWhereItem i : this)
        for (int idx=0;idx<this.size();idx++)
        {
        	SqlWhereItem i=this.get(idx);
            if (i.Value != null)
            {
                prev = count == 0 ?
                    (woa == WhereOrAnd.Where ? " where " : " and ")
                    : " and ";

                Object val = i.Value;
                //if (i.Value instanceof DateTime) { tmpControl.Text = PFDataHelper.ObjectToDateString(val, tmpControl.Attributes["dateFmt"]); }
                if (val instanceof String && !PFDataHelper.StringIsNullOrWhiteSpace(val.toString()))
                {
                    switch (i.ExpressionOperator)
                    {
                        //既然为空时不进入这里,那么isnull应该是无必要的--wxj20181022
                        case Like:
                            //result += prev + PFDataHelper.FormatString(" isnull({0},'') like '%{1}%' ", i.Key, i.Value);
                            result += prev + PFDataHelper.FormatString(" {0} like '%{1}%' ", FormatKey(i.Key), i.Value);
                            break;
                        case StartWith:
                            //result += prev + PFDataHelper.FormatString(" isnull({0},'') like '{1}%' ", i.Key, i.Value);
                            result += prev + PFDataHelper.FormatString(" {0} like '{1}%' ", FormatKey(i.Key), i.Value);
                            break;
                        case EndWith:
                            //result += prev + PFDataHelper.FormatString(" isnull({0},'') like '%{1}' ", i.Key, i.Value);
                            result += prev + PFDataHelper.FormatString(" {0} like '%{1}' ", FormatKey(i.Key), i.Value);
                            break;
                        case NotEqual:
                            result += prev + PFDataHelper.FormatString(" isnull({0},'') <> '{1}' ", FormatKey(i.Key), i.Value);
                            break;
                        default:
                            //result += prev + PFDataHelper.FormatString(" isnull({0},'')='{1}' ", i.Key, i.Value);
                            result += prev + PFDataHelper.FormatString(" {0}='{1}' ", FormatKey(i.Key), i.Value);
                            break;
                    }
                    count++;
                }
                else if (val instanceof BigDecimal || val instanceof Integer)
                {
                    //result += prev + PFDataHelper.FormatString(" isnull({0},0)={1} ", FormatKey(i.Key), i.Value);
                    switch (i.ExpressionOperator)
                    {
                        //日期的范围比较有些特别,把临界点包含进来比较适合
                        case Less:
                            //result += prev + PFDataHelper.FormatString(" isnull({0},'') <= '{1}' ", FormatKey(i.Key), i.Value);
                            result += prev + PFDataHelper.FormatString(" isnull({0},0)<={1} ", FormatKey(i.Key), i.Value);
                            break;
                        case Greater:
                            //result += prev + PFDataHelper.FormatString(" isnull({0},'') >= '{1}' ", FormatKey(i.Key), i.Value);
                            result += prev + PFDataHelper.FormatString(" isnull({0},0)>={1} ", FormatKey(i.Key), i.Value);
                            break;
                        default:
                            //result += prev + PFDataHelper.FormatString(" isnull({0},'')='{1}' ", FormatKey(i.Key), i.Value);
                            result += prev + PFDataHelper.FormatString(" isnull({0},0)={1} ", FormatKey(i.Key), i.Value);
                            break;
                    }
                    count++;
                }
                else if (val instanceof Calendar && val != null)
                {
              	   SimpleDateFormat sdf = new SimpleDateFormat(PFDataHelper.DateFormat);
             	   String dateStr = sdf.format(((Calendar)val).getTime());
                    switch (i.ExpressionOperator)
                    {

                        //日期的范围比较有些特别,把临界点包含进来比较适合
                        case Less:
                            result += prev + PFDataHelper.FormatString(" isnull({0},'') <= '{1}' ", FormatKey(i.Key), dateStr);
                            break;
                        case Greater:
                            result += prev + PFDataHelper.FormatString(" isnull({0},'') >= '{1}' ", FormatKey(i.Key), dateStr);
                            break;
                        default:
                            result += prev + PFDataHelper.FormatString(" isnull({0},'')='{1}' ", FormatKey(i.Key), dateStr);
                            break;
                    }
                    count++;
                }
                else if (val instanceof Boolean && val != null)
                {
                	Boolean b = (Boolean)val;
                    result += prev + PFDataHelper.FormatString(" {0}={1} ", FormatKey(i.Key), b ? 1 : 0);
                    count++;
                }
                else if (val instanceof UUID)
                {
                    result += prev + PFDataHelper.FormatString(" {0}='{1}' ", FormatKey(i.Key), i.Value);
                    count++;
                }
                else if (val instanceof List && i.ExpressionOperator == SqlExpressionOperator.IN)
                {
//                    var list = val as List<String>;
                	//((List)val).toArray();
                	//ArrayList<String> list=(ArrayList<String>)val;
                	//List list=val;
                	//List<String> list=(List<String>)val;
                	ArrayList<String> list=PFDataHelper.<String>ObjectToList(val);
                    //if (list.getItemCount() > 0)
                        if (list.size() > 0)
                    {
                        //result += prev + PFDataHelper.FormatString(" {0} in('{1}') ", FormatKey(i.Key), String.Join("','", list));
                        result += prev + PFDataHelper.FormatString(" {0} in('{1}') ", FormatKey(i.Key), String.join("','", list));//为了net2.0版本
                        count++;
                    }
                }
                else if (val instanceof List && i.ExpressionOperator == SqlExpressionOperator.NotIn)
                {
                    //var list = val as List<String>;
                    //List<String> list=(List<String>)val;
                	ArrayList<String> list=PFDataHelper.<String>ObjectToList(val);
                    if (list.size() > 0)
                    {
                        result += prev + PFDataHelper.FormatString(" {0} not in('{1}') ", FormatKey(i.Key), String.join("','", list));
                        count++;
                    }
                }
            }
        }
        return result;
    }
//    public String ToPageSql(Pagination pagination)
//    {
//        return PFDataHelper.FormatString(@"
//With TTTTT AS
//(
//{{0}}
//)
//select * from
//(
//select ROW_NUMBER() OVER (ORDER BY {0}) as rownumber,*,(select count(*) from TTTTT) as rowtotal from TTTTT
//) as T
//where rownumber between {1} and {2}
//", pagination.Sort, pagination.Start, pagination.End);
//    }
}
